package com.freshdirect.src.dao;

import com.freshdirect.src.model.Product;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

public class ProductDAOImpl implements ProductDAO{

    private DataSource dataSource;
    
    @Override
    public void setDataSource(String filename) {
        Properties props = new Properties();
        InputStream fis = null;
        MysqlDataSource mysqlDS = null;
        try {
            fis = getClass().getResourceAsStream("/"+filename+".properties");
            props.load(fis);
            mysqlDS = new MysqlDataSource();
            mysqlDS.setURL(props.getProperty("DB_URL"));
            mysqlDS.setUser(props.getProperty("DB_USERNAME"));
            mysqlDS.setPassword(props.getProperty("DB_PASSWORD"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        this.dataSource = mysqlDS;
    }
    
    @Override
    public int save(Product product) {
        String query = "call addproduct(?,?,?,?,?,?,?,?,?,?)";
        Connection con = null;
        CallableStatement cs = null;
        int id = 0;
        try{
            con = dataSource.getConnection();
            cs = con.prepareCall(query);
            cs.setString(1, product.getName());
            cs.setString(2, product.getDescription());
            cs.setInt(3, product.getProdGroup());
            cs.setDouble(4, product.getPrice());
            cs.setInt(5, product.getQuantity());
            cs.setInt(6, product.getProcurLevel());
            cs.setInt(7, product.getProcurQuantity());
            cs.setString(8, product.getImage());
            cs.setString(9,product.getSupsIntoString());
            System.out.println("ProductDAO -> saveProduct");
            cs.registerOutParameter(10, java.sql.Types.INTEGER);
            cs.execute();
            id = cs.getInt(10);
            if( id!=0){
                System.out.println("Product saved");
            }
            else{
                System.out.println("Product failed");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                cs.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }      
        }
        return id;
    }

    @Override
    public Product getById(int id) {
        String query = "call getproductbyid(?)";
        Product product = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, id);
            System.out.println(id);
            rs = ps.executeQuery();
            if(rs.next()){
                product = new Product();
                product.setId(id);
                product.setName(rs.getString(1));
                product.setDescription(rs.getString(2));
                product.setProdGroup(rs.getInt(3));
                product.setPrice(rs.getDouble(4));
                product.setQuantity(rs.getInt(5));
                product.setImage(rs.getString(6));
                System.out.println("Product Found::"+product);
            }else{
                System.out.println("No product found with id="+id);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return product;
       
    }

    @Override
    public List<Product> getAll() {
        String query = "call getproductsall()";
        List<Product> products = new ArrayList<Product>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            rs = ps.executeQuery();
            while(rs.next()){
                Product product = new Product();
                product.setId(rs.getInt(1));
                product.setName(rs.getString(2));
                product.setDescription(rs.getString(3));
                product.setProdGroup(rs.getInt(4));
                product.setPrice(rs.getDouble(5));
                product.setQuantity(rs.getInt(6));
                product.setProcurLevel(rs.getInt(7));
                product.setProcurQuantity(rs.getInt(8));
                product.setImage(rs.getString(9));
                products.add(product);
                System.out.println("Product Found::"+product);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return products;
    }

    @Override
    public List<Product> getProductsbyGroup(int id) {
        String query = "call getproductsbygroup(?)";
        List<Product> products = new ArrayList<Product>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while(rs.next()){
                Product product = new Product();
                product.setId(rs.getInt(1));
                product.setName(rs.getString(2));
                product.setDescription(rs.getString(3));
                product.setProdGroup(rs.getInt(4));
                product.setPrice(rs.getDouble(5));
                product.setQuantity(rs.getInt(6));
                product.setImage(rs.getString(7));
                products.add(product);
                System.out.println("Product Found::"+product);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return products;
       
    }

    @Override
    public List<Product> searchProducts(String groups, String suppliers, String keywords) {
        System.out.println("Groups: "+groups);
        System.out.println("Sups: "+suppliers);
        System.out.println(keywords);
        String query = "call browseproducts(?,?,?)";
        List<Product> products = new ArrayList<Product>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
           con = dataSource.getConnection();
           ps = con.prepareStatement(query);
           ps.setString(1,groups);
           ps.setString(2,suppliers);
           ps.setString(3,keywords);
           System.out.println(ps.toString());
           rs = ps.executeQuery();
           int id =0;
            while(rs.next()){
                if (rs.getInt(1) != id){
                    Product product = new Product();
                    id = rs.getInt(1);
                    product.setId(rs.getInt(1));
                    product.setName(rs.getString(2));
                    product.setPrice(rs.getDouble(3));
                    product.setQuantity(rs.getInt(4));
                    product.setImage(rs.getString(5));
                    product.setSupplierName(rs.getString(6));
                    products.add(product);
                    System.out.println("Product Found:"+product);
                }
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return products;
    }
    
    @Override
    public List<Product> getMostPopularProducts() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Product> getSuggestedbyId(int id) {
        String query = "call findsuggestions(?)";
        List<Product> products = new ArrayList<Product>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while(rs.next()){
                Product product = new Product();
                product.setId(rs.getInt(2));
                product.setName(rs.getString(3));
                product.setPrice(rs.getDouble(4));
                product.setQuantity(rs.getInt(5));
                product.setImage(rs.getString(6));
                products.add(product);
                System.out.println("Product Found::"+product);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return products;
    }

    @Override
    public void updateQuantity(int id, int n) {
        String query = "call updatequantity(?,?)";
        Product product = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, id);
            ps.setInt(2, n);
            rs = ps.executeQuery();
            
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public String getAllGroups() {
        String query = "call getallgroups();";
        String groups = "";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            rs = ps.executeQuery();
            while(rs.next()){
                groups += rs.getInt(1) + ",";
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return groups;
    }

    @Override
    public void update(Product product) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public void deleteById(int id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
    
  

    @Override
    public int findDegree(int sup1, int sup2) {
        String query = "call degreesofseparation(?,?)";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int degree = 0;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, sup1);
            ps.setInt(2, sup2);
            rs = ps.executeQuery();
            if(rs.next()){
               degree = rs.getInt(1);
            }else{
                System.out.println("No degree found");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return degree;
    }
    
    
    
}
